{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Collection\n",
    "\n",
    "1. Define Classes & Functions <br>\n",
    "    a. Get list of SEC docs for CIK<br>\n",
    "    b. Extract text, date, item numbers from each link<br>\n",
    "    c. Get price given ticker, date from Quandl, then AlphaVantage for missing data<br>\n",
    "    d. Get movement given ticker, date<br>\n",
    "    e. Get index movement<br>\n",
    "    f. Check if date is a weekday, and if necessary, adjust to Friday before<br>\n",
    "    g. Calculate dates for month before, quarter before, year before for historical movement calculations\n",
    "2. GET S&P 500 company info<br>\n",
    "3. Get list of 8K doc links <br>\n",
    "4. Download 8Ks & Stock Movements<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import datetime\n",
    "import unicodedata\n",
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from time import sleep\n",
    "import math\n",
    "import quandl\n",
    "from config import Config\n",
    "import dateutil.relativedelta\n",
    "import pandas_market_calendars as mcal\n",
    "import os\n",
    "import io\n",
    "import re\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1 Define Functions and Classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "class SEC_Extractor:\n",
    "    def get_doc_links(cik,ticker):\n",
    "        try:\n",
    "            base_url = \"https://www.sec.gov/cgi-bin/browse-edgar\"\n",
    "            inputted_cik = cik\n",
    "            payload = {\n",
    "                \"action\" : \"getcompany\",\n",
    "                \"CIK\" : inputted_cik,\n",
    "                \"type\" : \"8-K\",\n",
    "                \"output\":\"xml\",\n",
    "                \"dateb\" : \"20180401\",\n",
    "            }\n",
    "            sec_response = requests.get(url=base_url,params=payload)\n",
    "            soup = BeautifulSoup(sec_response.text,'lxml')\n",
    "            url_list = soup.findAll('filinghref')\n",
    "            html_list = []\n",
    "            # Get html version of links\n",
    "            for link in url_list:\n",
    "                link = link.string\n",
    "                if link.split(\".\")[len(link.split(\".\"))-1] == 'htm':\n",
    "                    txtlink = link + \"l\"\n",
    "                    html_list.append(txtlink)\n",
    "\n",
    "            doc_list = []\n",
    "            doc_name_list = []\n",
    "            # Get links for txt versions of files\n",
    "            for k in range(len(html_list)):\n",
    "                txt_doc = html_list[k].replace(\"-index.html\",\".txt\")\n",
    "                doc_name = txt_doc.split(\"/\")[-1]\n",
    "                doc_list.append(txt_doc)\n",
    "                doc_name_list.append(doc_name)\n",
    "                # Create dataframe of CIK, doc name, and txt link\n",
    "            df = pd.DataFrame(\n",
    "                {\n",
    "                \"cik\" : [cik]*len(html_list),\n",
    "                \"ticker\" : [ticker]*len(html_list),\n",
    "                \"txt_link\" : doc_list,\n",
    "                \"doc_name\": doc_name_list\n",
    "                }\n",
    "            )\n",
    "        except requests.exceptions.ConnectionError:\n",
    "                sleep(.1)\n",
    "        return df\n",
    "\n",
    "    # Extracts text and submission datetime from document link\n",
    "    def extract_text(link):\n",
    "        try:\n",
    "            r = requests.get(link)\n",
    "            #Parse 8-K document\n",
    "            filing = BeautifulSoup(r.content,\"html5lib\",from_encoding=\"ascii\")\n",
    "            #Extract datetime\n",
    "            try:\n",
    "                submission_dt = filing.find(\"acceptance-datetime\").string[:14]\n",
    "            except AttributeError:\n",
    "                    # Flag docs with missing data as May 1 2018 10AM\n",
    "                submission_dt = \"20180501100000\"\n",
    "            \n",
    "            submission_dt = datetime.datetime.strptime(submission_dt,\"%Y%m%d%H%M%S\")\n",
    "            #Extract HTML sections\n",
    "            for section in filing.findAll(\"html\"):\n",
    "                #Remove tables\n",
    "                for table in section(\"table\"):\n",
    "                    table.decompose()\n",
    "                #Convert to unicode\n",
    "                section = unicodedata.normalize(\"NFKD\",section.text)\n",
    "                section = section.replace(\"\\t\",\" \").replace(\"\\n\",\" \").replace(\"/s\",\" \").replace(\"\\'\",\"'\")\n",
    "            filing = \"\".join((section))\n",
    "        except requests.exceptions.ConnectionError:\n",
    "                sleep(10)\n",
    "        sleep(.1)\n",
    "\n",
    "        return filing, submission_dt\n",
    "\n",
    "    def extract_item_no(document):\n",
    "        pattern = re.compile(\"Item+ +\\d+[\\:,\\.]+\\d+\\d\")\n",
    "        item_list = re.findall(pattern,document)\n",
    "        return item_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Returns Dataframe of document links for a given CIK\n",
    "class FinDataExtractor:\n",
    "    def __init__(self,quandl_key,av_key):\n",
    "        # S&P 500 index data downloaded from Yahoo Finance GSPC\n",
    "        self.gspc_df = pd.read_csv(\"Data/Indexes/gspc.csv\",parse_dates=['Date'],index_col=\"Date\")\n",
    "        # Get VIX index data downloaded from Yahoo Finance\n",
    "        self.vix_df = pd.read_csv(\"Data/Indexes/vix.csv\",parse_dates=['Date'],index_col=\"Date\")\n",
    "        #Authenticate with API KEY\n",
    "        quandl.ApiConfig.api_key = quandl_key\n",
    "        self.av_key = av_key\n",
    "        nyse = mcal.get_calendar('NYSE')\n",
    "        self.nyse_holidays = nyse.holidays().holidays\n",
    "    \n",
    "#Takes datetime object and ticker string, returns price (opening or closing)\n",
    "    def get_historical_movements(self,row,period):\n",
    "        ticker,release_date = row[0],row[1]\n",
    "\n",
    "       #1 Week\n",
    "        if period == \"week\":\n",
    "            e_start = release_date + datetime.timedelta(weeks=-1)\n",
    "            b_start = e_start\n",
    "\n",
    "            e_end = release_date + dateutil.relativedelta.relativedelta(days=-1)\n",
    "            b_end = e_end\n",
    "\n",
    "         #1 Month    \n",
    "        elif period == \"month\":\n",
    "            e_start = release_date + dateutil.relativedelta.relativedelta(months=-1)\n",
    "            b_start = e_start + dateutil.relativedelta.relativedelta(days=-5)\n",
    "\n",
    "            e_end = release_date + dateutil.relativedelta.relativedelta(days=-1)\n",
    "            b_end = release_date + dateutil.relativedelta.relativedelta(days=-6)\n",
    "\n",
    "        #1 Quarter\n",
    "        elif period == \"quarter\":\n",
    "            e_start = release_date + dateutil.relativedelta.relativedelta(months=-3)\n",
    "            b_start = e_start + dateutil.relativedelta.relativedelta(days=-10)\n",
    "\n",
    "            e_end = release_date + dateutil.relativedelta.relativedelta(days=-1)\n",
    "            b_end = release_date + dateutil.relativedelta.relativedelta(days=-11)\n",
    "\n",
    "        #1 Year\n",
    "        elif period == \"year\":\n",
    "            e_start = release_date + dateutil.relativedelta.relativedelta(years=-1)\n",
    "            b_start = e_start + dateutil.relativedelta.relativedelta(days=-20)\n",
    "\n",
    "            e_end = release_date + dateutil.relativedelta.relativedelta(days=-1)\n",
    "            b_end = release_date + dateutil.relativedelta.relativedelta(days=-21)\n",
    "        else:\n",
    "            raise KeyError\n",
    "\n",
    "        e_start = self.weekday_check(e_start)\n",
    "        b_start = self.weekday_check(b_start)\n",
    "        e_end = self.weekday_check(e_end)\n",
    "        b_end = self.weekday_check(b_end)\n",
    "\n",
    "        start_price = self.get_quandl_data(ticker=ticker,start_date = b_start, end_date = e_start)\n",
    "        end_price = self.get_quandl_data(ticker=ticker,start_date = b_end, end_date = e_end)\n",
    "        stock_change = self.calculate_pct_change(end_price,start_price)\n",
    "\n",
    "        start_index = self.get_index_price(start_date = b_start, end_date = e_start)\n",
    "        end_index = self.get_index_price(start_date = e_start, end_date = e_end)\n",
    "        index_change =  self.calculate_pct_change(end_index,start_index)\n",
    "\n",
    "        normalized = stock_change - index_change\n",
    "        return normalized\n",
    "\n",
    "    def get_quandl_data(self,ticker,start_date,end_date,market_open=False):\n",
    "        if market_open == True:\n",
    "            quandl_param = \"WIKI/\" + ticker + \".8\"  \n",
    "        else:\n",
    "            quandl_param = \"WIKI/\" + ticker + \".11\" \n",
    "\n",
    "        end_date_str = datetime.datetime.strftime(end_date,\"%Y-%m-%d\") \n",
    "        start_date_str = datetime.datetime.strftime(start_date,\"%Y-%m-%d\")\n",
    "        price = quandl.get(quandl_param,start_date=start_date_str,end_date=end_date_str).mean()[0]\n",
    "\n",
    "        if np.isnan(price).any():\n",
    "            price = self.get_av_data(ticker,start_date,end_date,market_open)\n",
    "\n",
    "        return price\n",
    "\n",
    "    def get_av_data(self,ticker,start_date,end_date,market_open=False):\n",
    "        start_date = start_date.date()\n",
    "        end_date = end_date.date()\n",
    "\n",
    "        url = \"https://www.alphavantage.co/query?\"\n",
    "        params = {\"function\":\"TIME_SERIES_DAILY_ADJUSTED\",\n",
    "                  \"symbol\":ticker,\n",
    "                  \"datatype\":\"csv\",\n",
    "                  \"outputsize\":\"compact\",\n",
    "                  \"apikey\": self.av_key}\n",
    "        r = requests.get(url,params)\n",
    "        filepath = io.StringIO(r.content.decode('utf-8'))\n",
    "        av_df = pd.read_csv(filepath,parse_dates=True,index_col=[0],error_bad_lines=False)\n",
    "        try:\n",
    "            if market_open == False:\n",
    "                price = av_df.loc[end_date:start_date,\"adjusted_close\"].mean()\n",
    "            else:\n",
    "                price = av_df.loc[end_date:start_date,\"open\"].mean()\n",
    "        except (KeyError,IndexError):\n",
    "            price = np.nan\n",
    "        return price\n",
    "\n",
    "\n",
    "    # Takes ticker, 8K release date, checks time of release and then calculate before and after price change\n",
    "    def get_change(self,row):\n",
    "        release_date = row[1]\n",
    "        ticker = row[0]\n",
    "        market_close = release_date.replace(hour=16,minute=0,second=0)\n",
    "        market_open = release_date.replace(hour=9,minute=30,second=0)\n",
    "\n",
    "    # If report is released after market hours, take change of start date close and release date open\n",
    "        if release_date > market_close:\n",
    "            start_date = release_date\n",
    "            end_date = release_date + datetime.timedelta(days=1)\n",
    "            end_date = self.weekday_check(end_date)\n",
    "\n",
    "            price_before_release = self.get_quandl_data(ticker,start_date,start_date,market_open=False)\n",
    "            price_after_release = self.get_quandl_data(ticker,end_date,end_date,market_open=True)\n",
    "\n",
    "            index_before_release = self.get_index_price(start_date,start_date,market_open=False)\n",
    "            index_after_release = self.get_index_price(end_date,end_date,market_open=True)\n",
    "\n",
    "            try:\n",
    "                vix = self.vix_df.loc[self.vix_df.index == np.datetime64(start_date.date()),\"Adj Close\"][0].item()\n",
    "            except IndexError:\n",
    "                vix = np.nan\n",
    "\n",
    "        # If report is released before market hours, take change of start date's close and release date's open\n",
    "        elif release_date < market_open:\n",
    "            start_date = release_date + datetime.timedelta(days=-1)\n",
    "            start_date = self.weekday_check(start_date)\n",
    "            end_date = release_date\n",
    "\n",
    "            price_before_release = self.get_quandl_data(ticker,start_date,start_date,market_open=False)\n",
    "            price_after_release = self.get_quandl_data(ticker,end_date,end_date,market_open=True) \n",
    "\n",
    "            index_before_release = self.get_index_price(start_date,start_date,market_open=False)\n",
    "            index_after_release = self.get_index_price(end_date,end_date,market_open=True)\n",
    "            try:\n",
    "                vix = self.vix_df.loc[self.vix_df.index == np.datetime64(start_date.date()),\"Adj Close\"][0].item()\n",
    "            except IndexError:\n",
    "                vix = np.nan\n",
    "        # If report is released during market hours, use market close\n",
    "        else:\n",
    "            start_date = release_date\n",
    "            end_date = release_date\n",
    "            price_before_release = self.get_quandl_data(ticker,start_date,start_date,market_open=True)\n",
    "            price_after_release = self.get_quandl_data(ticker,end_date,end_date,market_open=False)\n",
    "\n",
    "            index_before_release = self.get_index_price(start_date,start_date,market_open=True)\n",
    "            index_after_release = self.get_index_price(end_date,end_date,market_open=False)\n",
    "            \n",
    "            try:\n",
    "                vix = self.vix_df.loc[self.vix_df.index == np.datetime64(start_date.date()),\"Open\"][0].item()\n",
    "            except IndexError:\n",
    "                vix = np.nan\n",
    "                \n",
    "        price_pct_change = self.calculate_pct_change(price_after_release,price_before_release)\n",
    "        index_pct_change = self.calculate_pct_change(index_after_release,index_before_release)\n",
    "        normalized_change = price_pct_change - index_pct_change\n",
    "\n",
    "        return normalized_change, vix\n",
    "\n",
    "    def get_index_price(self,start_date,end_date,market_open=False):\n",
    "        try:\n",
    "            if market_open == True:\n",
    "                price = self.gspc_df.loc[(self.gspc_df.index >= np.datetime64(start_date.date())) & \n",
    "                                 (self.gspc_df.index <= np.datetime64(end_date)),\"Open\"].mean()\n",
    "            else:\n",
    "                price = self.gspc_df.loc[(self.gspc_df.index >= np.datetime64(start_date.date())) & \n",
    "                                 (self.gspc_df.index <= np.datetime64(end_date)),\"Adj Close\"].mean()\n",
    "        except IndexError:\n",
    "                price = np.nan\n",
    "        return price\n",
    "\n",
    "    def calculate_pct_change(self,end_value,start_value):\n",
    "        pct_change = (end_value - start_value) / start_value\n",
    "        pct_change = round(pct_change,4) * 100\n",
    "        return pct_change\n",
    "\n",
    "    def weekday_check(self,date):  \n",
    "        while date.isoweekday() > 5 or date.date() in self.nyse_holidays:\n",
    "            date = date + datetime.timedelta(days=-1)\n",
    "        return date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Get S&P 500 Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Security</th>\n",
       "      <th>SEC filings</th>\n",
       "      <th>GICS Sector</th>\n",
       "      <th>GICS Sub Industry</th>\n",
       "      <th>Address of Headquarters</th>\n",
       "      <th>Date first added[3][4]</th>\n",
       "      <th>CIK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ticker symbol</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>MMM</th>\n",
       "      <td>3M Company</td>\n",
       "      <td>reports</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>St. Paul, Minnesota</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABT</th>\n",
       "      <td>Abbott Laboratories</td>\n",
       "      <td>reports</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>North Chicago, Illinois</td>\n",
       "      <td>1964-03-31</td>\n",
       "      <td>1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBV</th>\n",
       "      <td>AbbVie Inc.</td>\n",
       "      <td>reports</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>North Chicago, Illinois</td>\n",
       "      <td>2012-12-31</td>\n",
       "      <td>1551152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACN</th>\n",
       "      <td>Accenture plc</td>\n",
       "      <td>reports</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Dublin, Ireland</td>\n",
       "      <td>2011-07-06</td>\n",
       "      <td>1467373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ATVI</th>\n",
       "      <td>Activision Blizzard</td>\n",
       "      <td>reports</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Santa Monica, California</td>\n",
       "      <td>2015-08-31</td>\n",
       "      <td>718877</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Security SEC filings             GICS Sector  \\\n",
       "Ticker symbol                                                            \n",
       "MMM                     3M Company     reports             Industrials   \n",
       "ABT            Abbott Laboratories     reports             Health Care   \n",
       "ABBV                   AbbVie Inc.     reports             Health Care   \n",
       "ACN                  Accenture plc     reports  Information Technology   \n",
       "ATVI           Activision Blizzard     reports  Information Technology   \n",
       "\n",
       "                    GICS Sub Industry   Address of Headquarters  \\\n",
       "Ticker symbol                                                     \n",
       "MMM                       Industrials       St. Paul, Minnesota   \n",
       "ABT                       Health Care   North Chicago, Illinois   \n",
       "ABBV                      Health Care   North Chicago, Illinois   \n",
       "ACN            Information Technology           Dublin, Ireland   \n",
       "ATVI           Information Technology  Santa Monica, California   \n",
       "\n",
       "              Date first added[3][4]      CIK  \n",
       "Ticker symbol                                  \n",
       "MMM                              NaN    66740  \n",
       "ABT                       1964-03-31     1800  \n",
       "ABBV                      2012-12-31  1551152  \n",
       "ACN                       2011-07-06  1467373  \n",
       "ATVI                      2015-08-31   718877  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get table of the S&P 500 tickers, CIK, and industry from Wikipedia\n",
    "wiki_url = \"https://en.wikipedia.org/wiki/List_of_S%26P_500_companies\"\n",
    "cik_df = pd.read_html(wiki_url,header=[0],index_col=0)[0]\n",
    "cik_df['GICS Sector'] = cik_df['GICS Sector'].astype(\"category\")\n",
    "cik_df['GICS Sub Industry'] = cik_df['GICS Sector'].astype(\"category\")\n",
    "cik_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Get List of 8K links from SEC Edgar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "quandl_key = Config.quandl_api_key\n",
    "av_key = Config.av_api_key\n",
    "sec_ext = SEC_Extractor\n",
    "fin_data = FinDataExtractor(quandl_key,av_key)\n",
    "no_parts = 2\n",
    "part_no = 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 505/505 [07:38<00:00,  1.10it/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>cik</th>\n",
       "      <th>doc_name</th>\n",
       "      <th>txt_link</th>\n",
       "      <th>GICS Sector</th>\n",
       "      <th>GICS Sub Industry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1090872</td>\n",
       "      <td>0001564590-18-006570.txt</td>\n",
       "      <td>http://www.sec.gov/Archives/edgar/data/1090872...</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>1090872</td>\n",
       "      <td>0001090872-18-000002.txt</td>\n",
       "      <td>http://www.sec.gov/Archives/edgar/data/1090872...</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>1090872</td>\n",
       "      <td>0001564590-18-000605.txt</td>\n",
       "      <td>http://www.sec.gov/Archives/edgar/data/1090872...</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>1090872</td>\n",
       "      <td>0001090872-17-000015.txt</td>\n",
       "      <td>http://www.sec.gov/Archives/edgar/data/1090872...</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>1090872</td>\n",
       "      <td>0001090872-17-000011.txt</td>\n",
       "      <td>http://www.sec.gov/Archives/edgar/data/1090872...</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ticker      cik                  doc_name  \\\n",
       "0      A  1090872  0001564590-18-006570.txt   \n",
       "1      A  1090872  0001090872-18-000002.txt   \n",
       "2      A  1090872  0001564590-18-000605.txt   \n",
       "3      A  1090872  0001090872-17-000015.txt   \n",
       "4      A  1090872  0001090872-17-000011.txt   \n",
       "\n",
       "                                            txt_link  GICS Sector  \\\n",
       "0  http://www.sec.gov/Archives/edgar/data/1090872...  Health Care   \n",
       "1  http://www.sec.gov/Archives/edgar/data/1090872...  Health Care   \n",
       "2  http://www.sec.gov/Archives/edgar/data/1090872...  Health Care   \n",
       "3  http://www.sec.gov/Archives/edgar/data/1090872...  Health Care   \n",
       "4  http://www.sec.gov/Archives/edgar/data/1090872...  Health Care   \n",
       "\n",
       "  GICS Sub Industry  \n",
       "0       Health Care  \n",
       "1       Health Care  \n",
       "2       Health Care  \n",
       "3       Health Care  \n",
       "4       Health Care  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_list = []\n",
    "company_list = cik_df['CIK'].to_dict()\n",
    "for (ticker,cik) in tqdm(company_list.items()):\n",
    "    df_list.append(sec_ext.get_doc_links(cik,ticker))\n",
    "doc_links_df = pd.concat(df_list,axis=0)\n",
    "doc_links_df = doc_links_df.set_index(\"ticker\").join(cik_df['GICS Sector']).join(cik_df['GICS Sub Industry']).reset_index().rename(columns={\"index\":\"ticker\"})\n",
    "doc_links_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "doc_links_df.to_pickle(\"Pickles/doc_links_df.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Download 8Ks & Stock Movements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows to process at once (10 to 50 recommended)10\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "  0%|          | 0/1 [00:00<?, ?it/s]\u001b[A\n"
     ]
    },
    {
     "ename": "LimitExceededError",
     "evalue": "(Status 429) (Quandl Error QELx02) You have exceeded the API daily limit of 50000 calls per day. Please contact us if you need more than this volume of data on a regular basis.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mLimitExceededError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-10-f47ffb7ffaa2>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     14\u001b[0m     \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'text'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'release_date'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'txt_link'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msec_ext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mextract_text\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     15\u001b[0m     \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'items'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'text'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmap\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msec_ext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mextract_item_no\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 16\u001b[0;31m     \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'price_change'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'vix'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ticker'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'release_date'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfin_data\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_change\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mbroadcast\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     17\u001b[0m     \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'rm_week'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ticker'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'release_date'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfin_data\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_historical_movements\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mperiod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"week\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     18\u001b[0m     \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'rm_month'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ticker'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'release_date'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfin_data\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_historical_movements\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mperiod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"month\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mapply\u001b[0;34m(self, func, axis, broadcast, raw, reduce, args, **kwds)\u001b[0m\n\u001b[1;32m   4877\u001b[0m                         ignore_failures=ignore_failures)\n\u001b[1;32m   4878\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4879\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_apply_broadcast\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4880\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4881\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_apply_empty_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mreduce\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m_apply_broadcast\u001b[0;34m(self, func, axis)\u001b[0m\n\u001b[1;32m   5013\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtarget\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5014\u001b[0m         \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol\u001b[0m \u001b[0;32min\u001b[0m \u001b[0menumerate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5015\u001b[0;31m             \u001b[0mresult_values\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mi\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtarget\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mcol\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5016\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5017\u001b[0m         result = self._constructor(result_values, index=target.index,\n",
      "\u001b[0;32m<ipython-input-3-059efc008d3c>\u001b[0m in \u001b[0;36mget_change\u001b[0;34m(self, row)\u001b[0m\n\u001b[1;32m    117\u001b[0m             \u001b[0mend_date\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mweekday_check\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mend_date\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    118\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 119\u001b[0;31m             \u001b[0mprice_before_release\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_quandl_data\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mticker\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mstart_date\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mstart_date\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mmarket_open\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    120\u001b[0m             \u001b[0mprice_after_release\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_quandl_data\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mticker\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mend_date\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mend_date\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mmarket_open\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    121\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-3-059efc008d3c>\u001b[0m in \u001b[0;36mget_quandl_data\u001b[0;34m(self, ticker, start_date, end_date, market_open)\u001b[0m\n\u001b[1;32m     74\u001b[0m         \u001b[0mend_date_str\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstrftime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mend_date\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"%Y-%m-%d\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     75\u001b[0m         \u001b[0mstart_date_str\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstrftime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstart_date\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"%Y-%m-%d\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 76\u001b[0;31m         \u001b[0mprice\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquandl\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquandl_param\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mstart_date\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstart_date_str\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mend_date\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mend_date_str\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmean\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     77\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     78\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misnan\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mprice\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0many\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/quandl/get.py\u001b[0m in \u001b[0;36mget\u001b[0;34m(dataset, **kwargs)\u001b[0m\n\u001b[1;32m     46\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mdataset_args\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'column_index'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     47\u001b[0m             \u001b[0mkwargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mupdate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'column_index'\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mdataset_args\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'column_index'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 48\u001b[0;31m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mDataset\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdataset_args\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'code'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhandle_column_not_found\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     49\u001b[0m     \u001b[0;31m# Array\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     50\u001b[0m     \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdataset\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/quandl/model/dataset.py\u001b[0m in \u001b[0;36mdata\u001b[0;34m(self, **options)\u001b[0m\n\u001b[1;32m     45\u001b[0m         \u001b[0mupdated_options\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mUtil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge_options\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'params'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0moptions\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     46\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 47\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mData\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mupdated_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     48\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mNotFoundError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     49\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mhandle_not_found_error\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/quandl/operations/list.py\u001b[0m in \u001b[0;36mall\u001b[0;34m(cls, **options)\u001b[0m\n\u001b[1;32m     12\u001b[0m             \u001b[0moptions\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'params'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     13\u001b[0m         \u001b[0mpath\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mUtil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconstructed_path\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlist_path\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0moptions\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'params'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 14\u001b[0;31m         \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mConnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'get'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpath\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0moptions\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     15\u001b[0m         \u001b[0mresponse_data\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjson\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     16\u001b[0m         \u001b[0mUtil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconvert_to_dates\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/quandl/connection.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(cls, http_verb, url, **options)\u001b[0m\n\u001b[1;32m     34\u001b[0m         \u001b[0mabs_url\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'%s/%s'\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mApiConfig\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapi_base\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     35\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 36\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute_request\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mhttp_verb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mabs_url\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0moptions\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     37\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     38\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mclassmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/quandl/connection.py\u001b[0m in \u001b[0;36mexecute_request\u001b[0;34m(cls, http_verb, url, **options)\u001b[0m\n\u001b[1;32m     42\u001b[0m             \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0moptions\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     43\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatus_code\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m200\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatus_code\u001b[0m \u001b[0;34m>=\u001b[0m \u001b[0;36m300\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 44\u001b[0;31m                 \u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhandle_api_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     45\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     46\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/quandl/connection.py\u001b[0m in \u001b[0;36mhandle_api_error\u001b[0;34m(cls, resp)\u001b[0m\n\u001b[1;32m     83\u001b[0m         \u001b[0mklass\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0md_klass\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcode_letter\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mQuandlError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     84\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 85\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mklass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mresp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatus_code\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mresp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mresp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcode\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mLimitExceededError\u001b[0m: (Status 429) (Quandl Error QELx02) You have exceeded the API daily limit of 50000 calls per day. Please contact us if you need more than this volume of data on a regular basis."
     ]
    }
   ],
   "source": [
    "while part_no > no_parts:\n",
    "    no_parts = int(input(\"Split data into how many parts?\"))\n",
    "    part_no = int(input(\"Which part is this?\"))\n",
    "    \n",
    "chunksize = int(input(\"Number of rows to process at once (10 to 50 recommended)\"))\n",
    "\n",
    "#Load pickle\n",
    "crawled_df = np.array_split(pd.read_pickle(\"Pickles/doc_links_df.pkl\"),no_parts)[part_no-1][:10]\n",
    "crawled_len = len(crawled_df['txt_link'])\n",
    "chunks = math.ceil(crawled_len/chunksize)\n",
    "\n",
    "df_list = []\n",
    "for df in tqdm(np.array_split(crawled_df,chunks)):\n",
    "    df['text'], df['release_date'] = zip(*df['txt_link'].apply(sec_ext.extract_text))\n",
    "    df['items'] = df['text'].map(sec_ext.extract_item_no)\n",
    "    df[['price_change','vix']] = df[['ticker','release_date']].apply(fin_data.get_change,axis=1,broadcast=True)\n",
    "    df['rm_week'] = df[['ticker','release_date']].apply(fin_data.get_historical_movements,period=\"week\",axis=1)\n",
    "    df['rm_month'] = df[['ticker','release_date']].apply(fin_data.get_historical_movements,period=\"month\",axis=1)\n",
    "    df['rm_qtr'] = df[['ticker','release_date']].apply(fin_data.get_historical_movements,period=\"quarter\",axis=1)\n",
    "    df['rm_year'] = df[['ticker','release_date']].apply(fin_data.get_historical_movements,period=\"year\",axis=1)\n",
    "    if not os.path.isfile('texts.csv'): #If no file exists, create one with header\n",
    "        df.to_csv(\"Data/texts{}.csv.gzip\".format(part_no),chunksize=chunksize,compression=\"gzip\")\n",
    "    else: # else it exists so append without writing the header\n",
    "        df.to_csv(\"Data/texts{}.csv.gzip\".format(part_no),mode=\"a\",header=False,compression=\"gzip\",chunksize=chunksize)       \n",
    "    df_list.append(df)\n",
    "df = pd.concat(df_list)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Text PreProcessing\n",
    "1. Remove extra whitespace\n",
    "2. Tokenize\n",
    "3. Remove punctuation, stopwords, convert to lower case\n",
    "4. Lemmatize\n",
    "5. Load pre-trained word embeddings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import spacy\n",
    "from nltk.corpus import stopwords\n",
    "import string\n",
    "\n",
    "stop_words = stopwords.words(\"english\")\n",
    "nlp = spacy.load(\"en_core_web_sm\")\n",
    "punctuations = string.punctuation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanup_text(docs, logging=False):\n",
    "    docs = re.sub( '\\s+', ' ', docs ).strip()\n",
    "    texts = []\n",
    "    counter = 1\n",
    "    for doc in docs:\n",
    "        if counter % 1000 == 0 and logging:\n",
    "            print(\"Processed %d out of %d documents.\" % (counter, len(docs)))\n",
    "        counter += 1\n",
    "        doc = nlp(doc, disable=['parser', 'ner'])\n",
    "        tokens = [tok.lemma_.lower().strip() for tok in doc]\n",
    "        tokens = [tok for tok in tokens if tok.isalpha()]\n",
    "        tokens = [tok for tok in tokens if tok not in stop_words and tok not in punctuations]\n",
    "        tokens = ' '.join(tokens)\n",
    "        texts.append(tokens)\n",
    "    return texts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "text = df.iloc[4,6]\n",
    "cleanup_text(text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
